﻿using ICPFrameLib.Business;
using ICPFrameLib.Attributes;
using XHRPMIS.Common;
using EFFC.Frame.Net.Base.Common;
using EFFC.Frame.Net.Base.Data.Base;
using EFFC.Frame.Net.Module.Extend.EWRA.Attributes;
using EFFC.Frame.Net.Module.Extend.EWRA.Attributes.Validation;
using EFFC.Frame.Net.Unit.DB.Parameters;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using EFFC.Extends.LinqDLR2SQL;
using EFFC.Frame.Net.Unit.DB;
using EFFC.Frame.Net.Base.Data;
using System.IO;
using EFFC.Frame.Net.Base.Constants;
using System.Text.RegularExpressions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Text;
using ICPFrameLib.Common;
using EFFC.Frame.Net.Unit.DB.Datas;

namespace XHRPMIS.Business.v1._0
{
    public partial class MetaDynamicForm
    {
        #region OpenAPI
        [EWRARoute("post", "/md_form/{id}/export")]
        [EWRARouteDesc("导出")]
        [EWRAAddInput("id", "string", "元数据表uid", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.Path, false)]
        [EWRAAddInput("file_name", "string", "导出文件名称", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, false)]
        [EWRAAddInput("filter_column", "string", "过滤条件的栏位名称", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("filter_value", "string", "过滤条件的值", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("summary_column", "string", "汇总合计栏位名称，多个逗号分隔", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("summary_filter_express", "string", @"汇总合计过滤条件表达式，采用DBExpress的$where表达式写法，传入时采用base64+urlcode编码方式；
表达式中可以使用一些基础变量，如下：
__login_id:当前登录者ID,
__login_name:当前登录者的登录名称
__user_no:登录信息中的userno值,
__no:登录信息中的no值,
__user_name:登录信息中的username值,
__user_type:登录信息中的usertype值,
__role_no:当前登录者系统角色编号，多个,
__role_name:当前登录者系统角色名称，多个,
__now:当前时间，格式yyyy-MM-dd HH:mm:ss
", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("query_columns", "string", "要抓取的栏位，多个用逗号分隔", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("query_column_header", "string", "要抓取的表头名称，多个用逗号分隔", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("fixed_filter_express", "string", @"固定过滤条件表达式，采用DBExpress的$where表达式写法，传入时采用base64+urlcode编码方式；
表达式中可以使用一些基础变量，如下：
__login_id:当前登录者ID,
__login_name:当前登录者的登录名称
__user_no:登录信息中的userno值,
__no:登录信息中的no值,
__user_name:登录信息中的username值,
__user_type:登录信息中的usertype值,
__role_no:当前登录者系统角色编号，多个,
__role_name:当前登录者系统角色名称，多个,
__now:当前时间，格式yyyy-MM-dd HH:mm:ss
", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("fixed_orderby_express", "string", "固定排序表达式，采用DBExpress的$orderby表达式写法，传入时采用base64+urlcode编码方式", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("other_condition_express", "string", @"其它查询条件表达式，采用DBExpress的$orderby表达式写法，传入时采用base64+urlcode编码方式,
表达式中可以使用一些基础变量，如下：
界面上的参数用“@key@”来表示,
__login_id: 当前登录者ID,
__login_name: 当前登录者的登录名称
__user_no: 登录信息中的userno值,
__no: 登录信息中的no值,
__user_name: 登录信息中的username值,
__user_type: 登录信息中的usertype值,
__role_no: 当前登录者系统角色编号，多个,
__role_name: 当前登录者系统角色名称，多个,
__now: 当前时间，格式yyyy - MM - dd HH: mm:ss", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAOutputDesc("返回结果", @"如果是授权无效的情况下，返回400错，其它情况的返回结果集（状态为200）如下：{
code:'success-成功，failed-失败',
msg:'提示信息',
filetype:'文件的content-type类型',
filename:'文件名称',
filelength:'文件长度',
file:'文件内容，采用base64加密'
}")]
        object ExportExcel(string id)
        {
            SetCacheEnable(false);
            string file_name = ComFunc.nvl(PostDataD.file_name);
            string filter_column = ComFunc.nvl(PostDataD.filter_column);
            string filter_value = ComFunc.nvl(PostDataD.filter_value);
            string query_columns = ComFunc.nvl(PostDataD.query_columns);
            string query_column_header = ComFunc.nvl(PostDataD.query_column_header);
            string fixed_filter_express = ComFunc.UrlDecode(ComFunc.IsBase64Then(PostDataD.fixed_filter_express));
            string fixed_orderby_express = ComFunc.UrlDecode(ComFunc.IsBase64Then(PostDataD.fixed_orderby_express));
            string other_condition_express = ComFunc.UrlDecode(ComFunc.IsBase64Then(PostDataD.other_condition_express));
            string summary_column = ComFunc.nvl(PostDataD.summary_column);
            string summary_filter_express = ComFunc.UrlDecode(ComFunc.IsBase64Then(PostDataD.summary_filter_express));

            var orderby_fixed_filter_express = FrameDLRObject.IsJsonThen(fixed_orderby_express, null, FrameDLRFlags.SensitiveCase);
            var where_fixed_filter_express = parseFixedExpress(fixed_filter_express);
            var where_other_condition_express = parseFixedExpress(other_condition_express);
            var where_summary_filter_express = parseFixedExpress(summary_filter_express);

            if (fixed_filter_express != "" && where_fixed_filter_express == null)
            {
                return new
                {
                    code = "failed",
                    msg = "fixed_filter_express格式不正确"
                };
            }
            if (fixed_orderby_express != "" && orderby_fixed_filter_express == null)
            {
                return new
                {
                    code = "failed",
                    msg = "fixed_orderby_express格式不正确"
                };
            }

            var q_columns = query_columns.Split(",", StringSplitOptions.RemoveEmptyEntries);
            var q_headers = query_column_header.Split(",", StringSplitOptions.RemoveEmptyEntries);

            var metaup = DB.NewMetaUnitParameter();
            var up = DB.NewDBUnitParameter();
            var info = MetaDataUtilities.GetAllTables( metaup).Where(w => (w.MetaUID == id || w.MetaName == id) && w.IsCreated).FirstOrDefault();
            if(info == null)
            {
                return new
                {
                    code = "failed",
                    msg = "元数据表不存在",
                };
            }
            string tablename = info.MetaName;
            var columns = info.columns;
            // 固定带上以下栏位
            columns.AddRange(MetaDataUtilities.FixColumnsModel);
            //过滤要抓取的栏位
            columns = q_columns.Length <= 0 ? columns : columns.Where(d => q_columns.Contains(d.MetaColumnName)).ToList();
            #region 构建select表达式
            FrameDLRObject express = FrameDLRObject.CreateInstance(@"{
$acttype : 'Query',
                }", EFFC.Frame.Net.Base.Constants.FrameDLRFlags.SensitiveCase);

            FrameDLRObject tableexpress = FrameDLRObject.CreateInstance(EFFC.Frame.Net.Base.Constants.FrameDLRFlags.SensitiveCase);
            //order by
            FrameDLRObject orderby = orderby_fixed_filter_express == null ? FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase) : orderby_fixed_filter_express;
            orderby.SetValue("sort_no", "asc");
            express.SetValue("$orderby", orderby);

            foreach (var c in columns)
            {
                //设定select的栏位
                express.SetValue($"{c.MetaColumnName}", true);
            }
            express.SetValue("$table", tablename);
            FrameDLRObject where = where_fixed_filter_express == null ? FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase) : where_fixed_filter_express;
            if (!string.IsNullOrEmpty(filter_column))
            {
                var fc = filter_column.Split(',', StringSplitOptions.RemoveEmptyEntries);
                var orlist = new List<FrameDLRObject>();
                where.SetValue("$or", orlist);
                foreach (var f in fc)
                {
                    FrameDLRObject exp = FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase);
                    exp.SetValue("$like", filter_value);
                    var fexp = FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase);
                    fexp.SetValue(f, exp);
                    orlist.Add(fexp);
                }
            }
            //其它查询条件
            if (where_other_condition_express != null)
            {
                RemoveFilterEmptry(where_other_condition_express);
                foreach (var key in where_other_condition_express.Keys)
                {
                    if (ComFunc.nvl(where_other_condition_express.GetValue(key)) != "")
                    {
                        where.SetValue(key, where_other_condition_express.GetValue(key));
                    }
                }
            }

            if (where.Items.Count > 0)
            {
                express.SetValue("$where", where);
            }
            #endregion
            var result = DB.Excute(up, express, true);
            var data = result.QueryData<FrameDLRObject>();

            SetRefColumnsText(metaup, up, info, columns, data, false);
            //汇总合计
            if (summary_column != "")
            {
                FrameDLRObject sum_tableexpress = FrameDLRObject.CreateInstance(@"{
$acttype : 'Query',
                }", EFFC.Frame.Net.Base.Constants.FrameDLRFlags.SensitiveCase);

                var columns_arr = columns.Select(d => d.MetaColumnName).ToArray();
                var sum_column_arr = summary_column.Split(",", StringSplitOptions.RemoveEmptyEntries).Where(w => columns_arr.Contains(w)).ToArray();
                foreach (var c in sum_column_arr)
                {
                    //设定select的栏位
                    sum_tableexpress.SetValue($"{c}", true);
                }
                sum_tableexpress.SetValue("$table", tablename);
                if (where_other_condition_express != null)
                {
                    RemoveFilterEmptry(where_summary_filter_express);
                    foreach (var key in where_summary_filter_express.Keys)
                    {
                        if (ComFunc.nvl(where_summary_filter_express.GetValue(key)) != "")
                        {
                            where.SetValue(key, where_summary_filter_express.GetValue(key));
                        }
                    }
                }
                if (where.Items.Count > 0)
                {
                    sum_tableexpress.SetValue("$where", where);
                }
                var sum_result = DB.Excute(up, sum_tableexpress, true);
                var sum_resultlist = sum_result.QueryData<FrameDLRObject>();
                if (sum_resultlist.Count > 0)
                {
                    FrameDLRObject sum_item = FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase);
                    sum_item.SetValue(columns_arr[0], "合计");
                    foreach (var c in sum_column_arr)
                    {
                        sum_item.SetValue(c, sum_resultlist.Sum(s => DecimalStd.IsNotDecimalThen(s.GetValue(c))));
                    }
                    data.Add(sum_item);
                }
            }
            #region 生成excel
            //校正显示栏位,如果栏位有__text，则用__text栏位值替换原栏位
            var firstrow = data.FirstOrDefault();
            var new_columns = new List<string>();
            if (firstrow != null)
            {
                var keys = firstrow.Keys;
                foreach (var c in q_columns)
                {
                    if (keys.Contains($"{c}__text"))
                    {
                        new_columns.Add($"{c}__text");
                    }
                    else
                    {
                        new_columns.Add(c);
                    }
                }
            }
            else
            {
                new_columns.AddRange(q_columns);
            }
            byte[] bytes = Report.GenerateExcel(new_columns.ToArray(), q_headers, data);
            #endregion


            return new
            {
                code = "success",
                msg = "",
                filetype = ResponseHeader_ContentType.Map("xlsx"),
                filename = $"{(file_name == "" ? info.MetaDesc : file_name)}_{DateTime.Now.ToString("yyyyMMdd")}.xlsx",
                filelength = bytes.Length,
                file = ComFunc.Base64Code(bytes)
            };
        }
        #endregion

        #region LocalFunction

        #endregion
    }
}
